package com.hsnn.medstgmini.complain.dao.impl;

import java.util.List;
import java.util.UUID;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.complain.dao.SysComplainAttachmentDao;
import com.hsnn.medstgmini.complain.dao.SysComplainDao;
import com.hsnn.medstgmini.complain.model.SysComplain;
import com.hsnn.medstgmini.complain.model.SysComplainAttachment;
import com.hsnn.medstgmini.complain.model.SysComplainDelcomp;
import com.hsnn.medstgmini.complain.model.SysComplainHosp;
import com.hsnn.medstgmini.complain.model.SysComplainProd;
import com.hsnn.medstgmini.complain.model.SysComplainProdcomp;
import com.hsnn.medstgmini.complain.model.SysComplainProj;
import com.hsnn.medstgmini.util.Pagination;
/**
 * @category 申投诉 DAO实现类
 * @author 韩守松
 * @date 2015年9月8日
 */
@Repository
public class SysComplainDaoImpl  extends BaseHibernateDAO implements SysComplainDao{
	
	@Autowired
	private SysComplainAttachmentDao sysComplainAttachmentDao;
	/**
	 * @category 申投诉信息保存 
	 * @author 韩守松
	 * @date   2015年9月10日
	 * @param  @param sysComplain
	 * @param  @return
	 */
	public void save(SysComplain sysComplain) {
		sysComplain.setComplainId(UUID.randomUUID().toString());
		getSession().saveOrUpdate(sysComplain);
		getSession().flush();
		
		getSession().clear();
		StringBuffer hqlLog = new StringBuffer();
		hqlLog.append("insert into sys_complain_log  ")
		   .append(" select UUID(),sc.* from sys_complain sc where sc.complain_id= :complainId ");
		Query queryLog = getSession().createSQLQuery(hqlLog.toString());
		queryLog.setString("complainId", sysComplain.getComplainId());
		queryLog.executeUpdate();
		getSession().flush();
		
		//申投诉项目
			String projId =sysComplain.getProjId();
			if(StringUtils.isNotBlank(projId)){
					SysComplainProj sysComplainProj = new SysComplainProj();
					sysComplainProj.setSysComplainProj(UUID.randomUUID().toString());
					sysComplainProj.setComplainId(sysComplain.getComplainId());
					sysComplainProj.setProjId(projId);
					sysComplainProj.setUpdUser(sysComplain.getUpdUser());
					sysComplainProj.setUpdDatetime(sysComplain.getUpdDatetime());
					getSession().saveOrUpdate(sysComplainProj);
					getSession().flush();
					
					getSession().clear();
					StringBuffer hqlLogProj = new StringBuffer();
					hqlLogProj.append("insert into sys_complain_proj_log  ")
					   .append(" select UUID(),scproj.* from sys_complain_proj scproj where scproj.sys_complain_proj= :sysComplainProj ");
					Query queryLogProj = getSession().createSQLQuery(hqlLogProj.toString());
					queryLogProj.setString("sysComplainProj", sysComplainProj.getSysComplainProj());
					queryLogProj.executeUpdate();
					getSession().flush();
			}
		//申投诉医疗机构
			String[] hospId =sysComplain.getHospId();
			if(null != hospId){
				for(int i=0;i<hospId.length;i++){
					SysComplainHosp sysComplainHosp = new SysComplainHosp();
					sysComplainHosp.setSysComplainHosp(UUID.randomUUID().toString());
					sysComplainHosp.setComplainId(sysComplain.getComplainId());
					sysComplainHosp.setHospId(hospId[i]);
					sysComplainHosp.setUpdUser(sysComplain.getUpdUser());
					sysComplainHosp.setUpdDatetime(sysComplain.getUpdDatetime());
					getSession().saveOrUpdate(sysComplainHosp);
					getSession().flush();
					
					getSession().clear();
					StringBuffer hqlLogHosp = new StringBuffer();
					hqlLogHosp.append("insert into sys_complain_hosp_log  ")
					   .append(" select UUID(),scHosp.* from sys_complain_Hosp scHosp where scHosp.sys_complain_hosp= :sysComplainHosp ");
					Query queryLogHosp = getSession().createSQLQuery(hqlLogHosp.toString());
					queryLogHosp.setString("sysComplainHosp", sysComplainHosp.getSysComplainHosp());
					queryLogHosp.executeUpdate();
					getSession().flush();		
				}
			}
		//申投诉配送企业
			String[] delcompId =sysComplain.getDelcompId();
			if(null != delcompId){
				for(int i=0;i<delcompId.length;i++){
					SysComplainDelcomp sysComplainDelcomp = new SysComplainDelcomp();
					sysComplainDelcomp.setSysComplainDelcomp(UUID.randomUUID().toString());
					sysComplainDelcomp.setComplainId(sysComplain.getComplainId());
					sysComplainDelcomp.setDelcompId(delcompId[i]);
					sysComplainDelcomp.setUpdUser(sysComplain.getUpdUser());
					sysComplainDelcomp.setUpdDatetime(sysComplain.getUpdDatetime());
					getSession().saveOrUpdate(sysComplainDelcomp);
					getSession().flush();
					
					getSession().clear();
					StringBuffer hqlLogDelcomp = new StringBuffer();
					hqlLogDelcomp.append("insert into sys_complain_delcomp_log  ")
					   .append(" select UUID(),scdelcomp.* from sys_complain_delcomp scdelcomp where scdelcomp.sys_complain_delcomp= :sysComplainDelcomp ");
					Query queryLogDelcomp = getSession().createSQLQuery(hqlLogDelcomp.toString());
					queryLogDelcomp.setString("sysComplainDelcomp", sysComplainDelcomp.getSysComplainDelcomp());
					queryLogDelcomp.executeUpdate();
					getSession().flush();	
					}
				}
			
		//申投诉生产企业
			String[] compId =sysComplain.getCompId();
			if(null != compId){
				for(int i=0;i<compId.length;i++){
					SysComplainProdcomp sysComplainProdcomp = new SysComplainProdcomp();
					sysComplainProdcomp.setSysComplainProdcomp(UUID.randomUUID().toString());
					sysComplainProdcomp.setComplainId(sysComplain.getComplainId());
					sysComplainProdcomp.setProdcompId(compId[i]);
					sysComplainProdcomp.setUpdUser(sysComplain.getUpdUser());
					sysComplainProdcomp.setUpdDatetime(sysComplain.getUpdDatetime());
					getSession().saveOrUpdate(sysComplainProdcomp);
					getSession().flush();
					
					getSession().clear();
					StringBuffer hqlLogProdcomp = new StringBuffer();
					hqlLogProdcomp.append("insert into sys_complain_prodcomp_log  ")
					   .append(" select UUID(),scprodcomp.* from sys_complain_prodcomp scprodcomp where scprodcomp.sys_complain_prodcomp= :sysComplainProdcomp ");
					Query queryLogProdcomp = getSession().createSQLQuery(hqlLogProdcomp.toString());
					queryLogProdcomp.setString("sysComplainProdcomp", sysComplainProdcomp.getSysComplainProdcomp());
					queryLogProdcomp.executeUpdate();
					getSession().flush();
				}
			}
		//申投诉企业产品
			String[] drugId =sysComplain.getDrugId();
			if(null != drugId){
				for(int i=0;i<drugId.length;i++){
					SysComplainProd sysComplainProd = new SysComplainProd();
					sysComplainProd.setSysComplainDrug(UUID.randomUUID().toString());
					sysComplainProd.setComplainId(sysComplain.getComplainId());
					sysComplainProd.setProdId(drugId[i]);
					sysComplainProd.setUpdUser(sysComplain.getUpdUser());
					sysComplainProd.setUpdDatetime(sysComplain.getUpdDatetime());
					getSession().saveOrUpdate(sysComplainProd);
					getSession().flush();
					
					getSession().clear();
					StringBuffer hqlLogProd = new StringBuffer();
					hqlLogProd.append("insert into sys_complain_prod_log  ")
					   .append(" select UUID(),scprod.* from sys_complain_prod scProd where scprod.sys_complain_drug= :sysComplainDrug ");
					Query queryLogProd = getSession().createSQLQuery(hqlLogProd.toString());
					queryLogProd.setString("sysComplainDrug", sysComplainProd.getSysComplainDrug());
					queryLogProd.executeUpdate();
					getSession().flush();
				}
			}	
			
			
		//保存申投诉相关证明路径
		if(StringUtils.isNotBlank(sysComplain.getUrl())){
			String[] urls = sysComplain.getUrl().split(",");
			String[] attrNames =  sysComplain.getAttrName();
			for( int i=0;i<urls.length && i<attrNames.length ;i++ ){
					SysComplainAttachment sysComplainAttachment = new SysComplainAttachment();
					sysComplainAttachment.setSysComplainId(sysComplain.getComplainId());
					sysComplainAttachment.setUrl(urls[i]);
					sysComplainAttachment.setAttrName(attrNames[i]);
					sysComplainAttachment.setUpdDatetime(sysComplain.getUpdDatetime());
					sysComplainAttachment.setUpdUser(sysComplain.getUpdUser());
					sysComplainAttachmentDao.save(sysComplainAttachment);
			}
		}
	}
	
	/**
	 * @category 获取申投诉信息
	 * @author 韩守松
	 * @date   2015年9月10日
	 * @param  @param sysComplain
	 * @param  @return
	 */
	public Pagination getSysComplainDataList(SysComplain sysComplain) {
		Pagination page = new Pagination();
		StringBuffer hql = new StringBuffer();
		StringBuffer hqlCount = new StringBuffer();
		
		hql.append(" select  ");
		hql.append(" sc.name prosonName, ");
		hql.append(" sc.complain_id complainId, ");
		hql.append(" sc.complain_title complainTitle, ");
		hql.append(" sc.complain_hospital complainHospital, ");
		hql.append(" sc.complain_prodcomp complainProdcomp, ");
		hql.append(" scp.comp_name prodcompName, ");
		hql.append(" scp2.comp_name delcompName, ");
		hql.append(" shp.hosp_name hospName, ");
		hql.append(" sc.complain_delcomp complainDelcomp, ");
		hql.append(" DATE_FORMAT(sc.complain_datetime,'%Y-%m-%d %H:%i:%S') complainDatetime, ");
		hql.append(" sc.complain_type complainType, ");
		hql.append(" sc.complain_status complainStatus, ");
		hql.append(" supu.name processUsername, ");
		hql.append(" DATE_FORMAT(sc.process_datetime,'%Y-%m-%d %H:%i:%S') processDatetime, ");
		hql.append(" sufb.name feedbackUsername, ");
		hql.append(" DATE_FORMAT(sc.feedback_datetime,'%Y-%m-%d %H:%i:%S') feedbackDatetime, ");
		hql.append(" sc.upd_user updUser, ");
		hql.append(" su.name name, ");
		hql.append(" DATE_FORMAT(sc.upd_datetime,'%Y-%m-%d %H:%i:%S') updDatetime ");
		hql.append(" from sys_complain sc  ");
		hql.append(" left join std_compinfo scp on sc.complain_prodcomp = scp.comp_id ");
		hql.append(" left join std_compinfo scp2 on sc.complain_delcomp = scp2.comp_id ");
		hql.append(" left join std_hospinfo shp on sc.complain_hospital = shp.hosp_id ");  
		hql.append("left join  sys_user su on sc.upd_user = su.id ");
		hql.append("left join  sys_user supu on sc.process_username = supu.id ");
		hql.append("left join sys_user sufb on sc.feedback_username = sufb.id ");
		hql.append(" where 1=1 ");
		hql.append(" and (sc.complain_status = 1 or sc.complain_status = 2)  ");
		
		hqlCount.append(" select  ");
		hqlCount.append(" count(*)  ");
		hqlCount.append(" from sys_complain sc  ");
		hqlCount.append(" left join std_compinfo scp on sc.complain_prodcomp = scp.comp_id ");
		hqlCount.append(" left join std_compinfo scp2 on sc.complain_delcomp = scp2.comp_id ");
		hqlCount.append(" left join std_hospinfo shp on sc.complain_hospital = shp.hosp_id "); 
		hqlCount.append(" left join  sys_user su on sc.upd_user = su.id ");
		hqlCount.append(" where 1=1 ");
		hqlCount.append(" and (sc.complain_status = 1 or sc.complain_status = 2)  ");
		
		if(StringUtils.isNotBlank(sysComplain.getComplainTitle())){
			hql.append(" and sc.complain_title like :complainTitle ");
			hqlCount.append(" and sc.complain_title like :complainTitle ");
		}
		
		if(null != sysComplain.getComplainDatetime()){
			hql.append(" and DATE_FORMAT(sc.complain_datetime,'%Y-%m-%d') = :complainDatetime ");
			hqlCount.append(" and DATE_FORMAT(sc.complain_datetime,'%Y-%m-%d') = :complainDatetime ");
		}
		if(null != sysComplain.getComplainType()){
			hql.append(" and sc.complain_type = :complainType ");
			hqlCount.append(" and sc.complain_type = :complainType ");
		}
		
		hql.append(" order by  sc.complain_datetime desc ");
		Query queryCount = getSession().createSQLQuery(hqlCount.toString());
		
		
		if(StringUtils.isNotBlank(sysComplain.getComplainTitle())){
			queryCount.setString("complainTitle", "%"+sysComplain.getComplainTitle()+"%");
		}
		
		if(null != sysComplain.getComplainDatetime()){
			queryCount.setDate("complainDatetime", sysComplain.getComplainDatetime());
		}
		if(null != sysComplain.getComplainType()){
			queryCount.setInteger("complainType", sysComplain.getComplainType());
		}
		
		page.setRecords(Long.valueOf(queryCount.uniqueResult().toString()));
		
		Query query =  getSession().createSQLQuery(hql.toString());
		
		if(StringUtils.isNotBlank(sysComplain.getComplainTitle())){
			query.setString("complainTitle", "%"+sysComplain.getComplainTitle()+"%");
		}
		
		if(null != sysComplain.getComplainDatetime()){
			query.setDate("complainDatetime", sysComplain.getComplainDatetime());
		}
		if(null != sysComplain.getComplainType()){
			query.setInteger("complainType", sysComplain.getComplainType());
		}
		
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		page.setRows(query.list());
		return page;
	}
	
	/**
	 * @category 根据申投诉id 获取申投诉信息
	 * @author 韩守松
	 * @date   2015年9月11日
	 * @param  @param complainId
	 * @param  @return
	 */
	@SuppressWarnings("rawtypes")
	public List getSysComplainById(String complainId) {
		StringBuffer hql = new StringBuffer();
		hql.append(" select ");
		hql.append(" sc.name prosonName, ");
		hql.append(" sc.sex, ");
		hql.append(" sc.org_id orgId, ");
		hql.append(" sc.org_type orgType, ");
		hql.append(" sc.phone_num phoneNum, ");
		hql.append(" sc.email, ");
		hql.append(" sc.id_num idNum, ");
		hql.append(" sc.address, ");
		hql.append(" sc.zipcode, ");
		hql.append(" sc.complain_id complainId, ");
		hql.append(" sc.complain_title complainTitle, ");
		hql.append(" sc.complain_content complainContent, ");
		hql.append(" sc.complain_hospital complainHospital, ");
		hql.append(" sc.complain_prodcomp complainProdcomp, ");
		hql.append(" scp.comp_name prodcompName, ");
		hql.append(" scd.comp_name delcompName, ");
		hql.append(" sht.hosp_name hospName, ");
		hql.append(" sc.complain_delcomp complainDelcomp, ");
		hql.append(" DATE_FORMAT(sc.complain_datetime,'%Y-%m-%d %H:%i:%S') complainDatetime, ");
		hql.append(" sc.complain_remark complainRemark, ");
		hql.append(" sc.complain_type complainType, ");
		hql.append(" sc.complain_status complainStatus, ");
		hql.append(" sc.process_remark processRemark, ");
		hql.append(" supu.name processUsername, ");
		hql.append(" DATE_FORMAT(sc.process_datetime,'%Y-%m-%d %H:%i:%S') processDatetime, ");
		hql.append(" sc.process_feedback processFeedback, ");
		hql.append(" sufb.name feedbackUsername, ");
		hql.append(" DATE_FORMAT(sc.feedback_datetime,'%Y-%m-%d %H:%i:%S') feedbackDatetime, ");
		hql.append(" sc.upd_user updUser, ");
		hql.append(" su.name name, ");
		hql.append(" DATE_FORMAT(sc.upd_datetime,'%Y-%m-%d %H:%i:%S') updDatetime ");
		hql.append(" from sys_complain sc  ");
		hql.append("left join std_compinfo scp on sc.complain_prodcomp = scp.comp_id ");
		hql.append("left join std_compinfo scd on sc.complain_delcomp = scd.comp_id ");
		hql.append("left join std_hospinfo sht on sc.complain_hospital = sht.hosp_id ");
		hql.append("left join sys_user su on sc.upd_user = su.id ");
		hql.append("left join sys_user supu on sc.process_username = supu.id ");
		hql.append("left join sys_user sufb on sc.feedback_username = sufb.id ");
		hql.append(" where sc.complain_id = :complainId ");
		
		Query query = getSession().createSQLQuery(hql.toString());
		query.setString("complainId", complainId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}
	
	
	/**
	 * @category 申投诉 信息更改
	 * @author 韩守松
	 * @date   2015年9月14日
	 * @param  @param sysComplain
	 * @param  @return
	 */
	public void updata(SysComplain sysComplain){
		SysComplain sysComplainDeom = new SysComplain();
		sysComplainDeom = getSysComplainByComplainId(sysComplain.getComplainId());
		if(1 == sysComplain.getUserType()){
			sysComplainDeom.setProcessRemark(sysComplain.getProcessRemark());//处理备注
			sysComplainDeom.setProcessUsername(sysComplain.getProcessUsername());//处理人
			sysComplainDeom.setProcessDatetime(sysComplain.getProcessDatetime());//处理时间
			sysComplainDeom.setComplainStatus(sysComplain.getComplainStatus());//处理状态
		}else{
			sysComplainDeom.setProcessFeedback(sysComplain.getProcessFeedback());//反馈备注
			sysComplainDeom.setFeedbackUsername(sysComplain.getFeedbackUsername());//反馈人
			sysComplainDeom.setFeedbackDatetime(sysComplain.getFeedbackDatetime());//反馈时间
		}
		sysComplainDeom.setUpdUser(sysComplain.getUpdUser());
		sysComplainDeom.setUpdDatetime(sysComplain.getUpdDatetime());
		getSession().saveOrUpdate(sysComplainDeom);
		getSession().flush();
		
		getSession().clear();
		StringBuffer hqlLog = new StringBuffer();
		hqlLog.append("insert into sys_complain_log  ")
		   .append(" select UUID(),sc.* from sys_complain sc where sc.complain_id= :complainId ");
		Query queryLog = getSession().createSQLQuery(hqlLog.toString());
		queryLog.setString("complainId", sysComplain.getComplainId());
		queryLog.executeUpdate();
		getSession().flush();
	}
	
	public SysComplain getSysComplainByComplainId(String complainId){
		StringBuffer hql =  new StringBuffer();
		hql.append(" from SysComplain sc where sc.complainId = :complainId ");
		Query query = getSession().createQuery(hql.toString());
		query.setString("complainId", complainId);
		return (SysComplain)query.uniqueResult();
	}
	
	/**
	 * @category 申投诉状态变更
	 * @author 韩守松
	 * @date   2015年9月14日
	 * @param  @param sysComplain
	 */
	public void updataStatus(SysComplain sysComplain){
		StringBuffer hql =  new StringBuffer();
		hql.append("update SysComplain set complainStatus = 1 where complainId = :complainId ");
		Query query = getSession().createQuery(hql.toString());
		query.setString("complainId", sysComplain.getComplainId());
		query.executeUpdate();
		getSession().flush();
		
		getSession().clear();
		StringBuffer hqlLog = new StringBuffer();
		hqlLog.append("insert into sys_complain_log  ")
		   .append(" select UUID(),sc.* from sys_complain sc where sc.complain_id= :complainId ");
		Query queryLog = getSession().createSQLQuery(hqlLog.toString());
		queryLog.setString("complainId", sysComplain.getComplainId());
		queryLog.executeUpdate();
		getSession().flush();
		
	}
}
